Différentes méthodes

• méthode "classique" par sauvegarde / restauration :
• simple et robuste
• pas de connexion possible durant la migration
• durée d'indisponibilité proportionnelle à la volumétrie
• pg_upgrade :
• copie physique des fichiers de données
• méthode un peu plus complexe (librairies PostGIS)
• pas de connexion possible durant la migration (mais moins longtemps)
• Réplication Slony / pg_logical :
• temps d’interruption fortement diminué
• nécessite installation et configuration d’outils externes
• besoin de compétences supplémentaires

Réplication Logique

• solution native
• disponible depuis la v10
• temps d’interruption fortement diminué
• des étapes à respecter
• quelques particularités liées à PostGIS

Vérifications préalables

• lister les bases avec leur volumétrie : \l+ (psql)
• vérifier l’espace disque disponible (surtout si la nouvelle instance est sur le même serveur)
• pour chaque base, liste des extensions installées : \dx (psql)
• pour chaque base, vérifier que toutes les tables ont une clé primaire :

    SELECT tbl.table_schema, tbl.table_name
    FROM information_schema.tables tbl
    WHERE table_type = 'BASE TABLE'
    AND table_schema NOT IN ('pg_catalog', 'information_schema')
    AND NOT EXISTS (
        SELECT 1
        FROM information_schema.key_column_usage kcu
        WHERE kcu.table_name = tbl.table_name
        AND kcu.table_schema = tbl.table_schema
    )

Génération de clés primaires automatiques

WITH cles_abs AS (
    SELECT tbl.table_schema, tbl.table_name
    FROM information_schema.tables tbl
    WHERE table_type = 'BASE TABLE'
    AND table_schema NOT IN ('pg_catalog', 'information_schema')
    AND NOT EXISTS (
        SELECT 1
        FROM information_schema.key_column_usage kcu
        WHERE kcu.table_name = tbl.table_name
        AND kcu.table_schema = tbl.table_schema
    )
)
SELECT 'ALTER TABLE' || table_schema || '.' || table_name 
    || ' ADD COLUMN id INT4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY;'
FROM cles_abs
ORDER BY table_schema, table_name;

• même principe pour préparer les requêtes supprimant ces clés en fin de migration (si voulu)

Préparation de la réplication logique et sauvegarde

• wal_level = logical
• redémarrage de l’instance
• faire une sauvegarde complète, y compris les fichiers de configuration

pg_dumpall | gzip -c > fichier_sauvegarde.sql.gz

Installation et paramétrage

• depuis les dépôts communautaires
• installer les packages nécessaires aux extensions (PostGIS notamment)
• mise à jour de quelques paramètres de configuration :
• port d’écoute
• shared_buffers (attention si la nouvelle instance est sur le même serveur que l’ancienne)
• effective_cache_size
• max_worker_processes = max_logical_replication_workers + 1
• redémarrer la nouvelle instance

Recopie de la structure et des rôles

• fin des changements de structure (ordres DDL)
• sauvegarde de la structure (sans les données) de l’ancienne instance
• sauvegarde des rôles de connexion de l’ancienne instance
• restauration des deux sur la nouvelle instance
• rediriger les erreurs et les analyser
• pour deux instances sur la même machine, sous Linux :

pg_dumpall -p 5432 -s | psql -p 5433 2> erreurs.txt

Cas particulier du passage à PostGIS 3

• les tables avec colonnes raster font partie des erreurs de l’étape précédente
• fonctions et types raster dans une nouvelle extension postgis_raster
• se connecter à la nouvelle instance et installer l’extension postgis_raster dans chaque base où elle est nécessaire
• réexporter la structure des tables avec colonne raster de l’ancienne instance
• l’importer dans la nouvelle instance et vérifier les erreurs
• pour deux instances sur la même machine, sous Linux :

pg_dump -Fp -p 5432 -s -t "tbl_rast” -d base_raster > table_raster.sql
psql -p 5433 -f table_raster.sql -d base_raster 2> erreurs_raster.txt

Principes

Publications sur l’ancienne instance

• dans chaque base de données
• créer une publication pour toutes les tables de la base
• nommer différemment chaque publication
• exemple de commande SQL :

CREATE PUBLICATION publi1 FOR ALL TABLES;

Cas particulier de la table spatial_ref_sys

• table présente et préremplie à l’installation de l’extension PostGIS
• contient toutes les références de projections spatiales
• son contenu dépend des versions de PostGIS (ajouts)
• si on la laisse, conflit de réplication
• sur la nouvelle instance, pour chaque base avec l’extension PostGIS, la recopier dans une autre table puis la vider :

CREATE TABLE spatial_ref_2 AS
SELECT *
FROM spatial_ref_sys;
DELETE FROM spatial_ref_sys;

Souscriptions sur la nouvelle instance

• dans chaque base de données
• créer une souscription vers la publication correspondante
• nommer différemment chaque souscription
• exemple de commande SQL :

CREATE SUBSCRIPTION souscript1 
    CONNECTION 'port=5432 user=postgres dbname=ma_base_1' PUBLICATION publi1;

Suivi de l’avancement de la réplication

• sur la nouvelle instance
• interroger la vue système pg_subscription_rel
• la colonne srsubstate donne l’état de la souscription de chaque table :
• i = initialisation
• d = copie des données en cours
• s = synchronisé
• r = prêt
• la recopie des données est terminée quand toutes les tables sont à l’état r
• les modifications des données de l’ancienne instance continuent d’être répliquées

Coupure de l’accès à l’ancienne instance

• on commence par bloquer les nouveaux accès aux différentes bases
• interroger la vue système pg_stat_activity pour lister les connexions résiduelles
• on stoppe les connexions en cours (manuellement ou automatiquement)
• modifier pg_hba.conf pour éviter toute reconnexion

ALTER DATABASE ma_base_1 CONNECTION LIMIT 0;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'ma_base_1'
AND pid <> pg_backend_pid()
AND backend_type = 'client_backend';

Recopie de l’avancement des séquences

• la réplication logique ne rejoue pas l’avancement des séquences
• il faut donc les mettre à jour sur la nouvelle instance depuis l’ancienne
• pour chaque base de données, recopier l’état d’avancement des différentes séquences
• plusieurs solutions possibles : commandes shell, SQL dynamique dans psql, pg_dump, etc.
• exemple de code bash pour l’automatiser :

psql -p 5432 -XAtqc "SELECT \$\$select setval('\$\$ || quote_ident(schemaname)||\$\$.\$\$|| quote_ident(sequencename) || \$\$', \$\$ || last_value || \$\$); \$\$ AS sql FROM pg_sequences" ma_base_1 \
| psql -p 5433 ma_base_1

Basculement sur la nouvelle instance

• mise à jour des paramètres de configuration (port, shared_buffers, etc.)
• recopie des fichiers pg_hba.conf et pg_ident.conf
• modification des paramètres de connexion des applications (si nécessaire)
• arrêt de l’ancienne instance
• redémarrage de la nouvelle instance
• suppression des souscriptions pour chaque base sur la nouvelle instance :

ALTER SUBSCRIPTION souscript1 DISABLE;
ALTER SUBSCRIPTION souscript1 SET (slot_name = NONE);
DROP SUBSCRIPTION souscript1;

Mise à niveau de spatial_ref_sys

• pour chaque base PostGIS, spatial_ref_sys contient les anciennes références
• récupération des nouvelles références depuis la copie précédente
• puis suppression de la table de copie

WITH delta AS (
    SELECT srid
    FROM spatial_ref_2
    EXCEPT
    SELECT srid
    FROM spatial_ref_sys
) 
INSERT INTO spatial_ref_sys
SELECT *
FROM delta;

DROP TABLE spatial_ref_2;

Une méthode efficace

• la réplication logique permet de préparer la migration sans couper l’instance de production
• elle permet de faire le basculement vers la nouvelle version quand tout y est prêt
• le temps d’indisponibilité est très faible (< 5 min)
• il peut être encore réduit par automatisation et pooler de connexions

Une prise en main plus délicate

• une méthode moins simple que les autres méthodes natives
• la complexité augmente avec le nombre de bases sur l’instance (paramétrage du nombre de souscriptions)
• elle nécessite de se l’approprier par la pratique et la répétition
• comme pour toute montée de version, testez, testez et testez au préalable

Merci de votre attention

Des questions ?     #pgsession13